package server;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;


import common.Item;
import common.ItemImpl;
import common.NameOccupiedException;



public class DBConnection {
	private static final String COUNT_USER			= "SELECT COUNT(*) AS CNT FROM USERS WHERE name = ?";
	private static final String SELECT_USER 		= "SELECT * FROM USERS WHERE name = ?";
	private static final String INSERT_USER 		= "INSERT INTO USERS (name, passwd, itemsSold, itemsBought, loggedIn) VALUES (?, ?, ?, ?, ?)";
	private static final String ALL_WATCHED_ITEMS 	= "SELECT * FROM WATCHEDITEMS";
	private static final String ALL_ITEMS_FOR_SALE 	= "SELECT * FROM ITEMSFORSALE";
	private static final String CHECK_PASSWORD 		= "SELECT COUNT(*) AS CNT FROM USERS WHERE name = ? AND passwd = ?";
	private static final String SET_LOGIN_STATUS	= "UPDATE USERS SET loggedIn = ? WHERE name = ?";
	private static final String GET_LOGIN_STATUS	= "SELECT loggedIn FROM USERS WHERE name = ?";
	private static final String INS_ITEM_FOR_SALE 	= "INSERT INTO ITEMSFORSALE (owner, name, price) VALUES (?, ?, ?)";
	private static final String INS_WATCHED_ITEM 	= "INSERT INTO WATCHEDITEMS (owner, name, maxPrice) VALUES (?, ?, ?)";
	private static final String DELETE_ITEM_FOR_SALE	= "DELETE FROM ITEMSFORSALE WHERE id = ?";
	private static final String DELETE_WATCHED_ITEM 	= "DELETE FROM WATCHEDITEMS WHERE id = ?";
	private static final String INCREMENT_ITEMS_SOLD	= "UPDATE USERS SET itemsSold = itemsSold + 1 WHERE name = ?";
	private static final String INCREMENT_ITEMS_BOUGHT 	= "UPDATE USERS SET itemsBought = itemsBought + 1 WHERE name = ?";
	private static final String GET_ITEMS_SOLD 			= "SELECT itemsSold FROM USERS WHERE name = ?";
	private static final String GET_ITEMS_BOUGHT 		= "SELECT itemsBought FROM USERS WHERE name = ?";
	private static final String LOGOUT_ALL_TRADERS 		= "UPDATE USERS SET loggedIn = 0";

	Connection conn;
	Marketplace marketplace;

	public DBConnection(String URL, String datasource, String username, String password, Marketplace marketplace) throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://" + URL + ":3306/" + datasource, username, password);
		this.marketplace = marketplace;
	}
	
	public void logoutAllTraders() throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(LOGOUT_ALL_TRADERS);
		stmnt.executeUpdate();
	}
	
	public void incrementItemsSold(String name) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(INCREMENT_ITEMS_SOLD);
		stmnt.setString(1, name);
		stmnt.executeUpdate();
	}
	
	public void incrementItemsBought(String name) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(INCREMENT_ITEMS_BOUGHT);
		stmnt.setString(1, name);
		stmnt.executeUpdate();
	}
	
	public int getItemsBought(String name) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(GET_ITEMS_BOUGHT);
		stmnt.setString(1, name);
		ResultSet rs = stmnt.executeQuery();
		rs.next();
		return rs.getInt("itemsBought");
	}
	
	public int getItemsSold(String name) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(GET_ITEMS_SOLD);
		stmnt.setString(1, name);
		ResultSet rs = stmnt.executeQuery();
		rs.next();
		return rs.getInt("itemsSold");
	}
	
	public void removeSoldItem(Item item) throws RemoteException, SQLException
	{
		
		PreparedStatement stmnt =  conn.prepareStatement(DELETE_ITEM_FOR_SALE);
		stmnt.setInt(1, item.getId());
		stmnt.executeUpdate();
	}
	
	public void removeWatchedItem(WatchedItem item) throws SQLException
	{
		PreparedStatement stmnt =  conn.prepareStatement(DELETE_WATCHED_ITEM);
		stmnt.setInt(1, item.getId());
		stmnt.executeUpdate();
	}
	public boolean usernameAvailable(String username) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(COUNT_USER);
		stmnt.setString(1, username);
		ResultSet rs = stmnt.executeQuery();
		rs.next();
		if (rs.getInt("CNT") == 0)
			return true;
		else
			return false;
	}
	
	public void registerUser(String name, String password) throws NameOccupiedException, SQLException {
		PreparedStatement selectAllStmt = conn.prepareStatement(SELECT_USER);
		selectAllStmt.setString(1, name);
		ResultSet rs = selectAllStmt.executeQuery();
		//RS empty -> no users with that name. Else throw exception.
		if (!rs.next()){
			PreparedStatement insertUserStmt = conn.prepareStatement(INSERT_USER);
			insertUserStmt.setString(1, name);
			insertUserStmt.setString(2, password);
			insertUserStmt.setInt(3, 0);
			insertUserStmt.setInt(4, 0);
			insertUserStmt.setBoolean(5, true);
			insertUserStmt.executeUpdate();
		} else {
			throw new NameOccupiedException(name);
		}
	}
	
	public boolean login(String name, String password) throws SQLException
	{
		PreparedStatement stmnt = conn.prepareStatement(CHECK_PASSWORD);
		stmnt.setString(1, name);
		stmnt.setString(2, password);
		ResultSet rs = stmnt.executeQuery();
		rs.next();
		if (rs.getInt("CNT") == 1)
		{
			setLoginStatus(name,true);
			return true;
		} else
			return false;
		
	}
	public void logout(String name) throws SQLException
	{
		setLoginStatus(name,false);
	}
	
	private void setLoginStatus(String name,boolean status) throws SQLException
	{
		PreparedStatement setLoggedIn = conn.prepareStatement(SET_LOGIN_STATUS);
		setLoggedIn.setBoolean(1, status);
		setLoggedIn.setString(2, name);
		setLoggedIn.executeUpdate();
	}
	
	public boolean getLoginStatus(String name) throws SQLException {
		PreparedStatement getLoggedIn = conn.prepareStatement(GET_LOGIN_STATUS);
		getLoggedIn.setString(1, name);
		ResultSet rs = getLoggedIn.executeQuery();
		if (rs.next()){
			if (rs.getBoolean("loggedIn")){
				return true;
			}
		} else {
			System.out.println("No login status recieved");
		}
		return false;
	}
	
	public LinkedList<WatchedItem> getWatchedItems() throws SQLException{
		LinkedList<WatchedItem> watchedList = new LinkedList<WatchedItem>();
		PreparedStatement watchedStmt = conn.prepareStatement(ALL_WATCHED_ITEMS);
		ResultSet rs = watchedStmt.executeQuery();
		while(rs.next()){
			WatchedItem wi = new WatchedItem(rs.getInt("id"),rs.getString("owner"), rs.getString("name"), rs.getFloat("maxPrice"));
			watchedList.add(wi);
		}
		return watchedList;
	}
	
	public LinkedList<Item> getItemsForSale() throws RemoteException, SQLException{
		LinkedList<Item> itemsList = new LinkedList<Item>();
		PreparedStatement forSaleStmt = conn.prepareStatement(ALL_ITEMS_FOR_SALE);
		ResultSet rs = forSaleStmt.executeQuery();
		while(rs.next()){
			Item item = new ItemImpl(rs.getInt("id"),rs.getString("owner"), rs.getString("name"), rs.getFloat("price"));
			itemsList.add(item);
		}
		return itemsList;
	}
	
	public void addItemForSale(Item item) throws RemoteException, SQLException{
		addItemForSale(item.getOwner(), item.getName(), item.getPrice());
	}
	
	public void addItemForSale(String owner, String name, float price) throws SQLException{
		PreparedStatement addItemStmt = conn.prepareStatement(INS_ITEM_FOR_SALE);
		addItemStmt.setString(1, owner);
		addItemStmt.setString(2, name);
		addItemStmt.setFloat(3, price);
		addItemStmt.executeUpdate();
	}
	
	public void addWatchedItem(WatchedItem wi) throws RemoteException, SQLException{
		addWatchedItem(wi.getTraderName(), wi.getItemName(), wi.getMaxprice());
	}
	
	public void addWatchedItem(String owner, String name, float maxPrice) throws SQLException{
		PreparedStatement addItemStmt = conn.prepareStatement(INS_WATCHED_ITEM);
		addItemStmt.setString(1, owner);
		addItemStmt.setString(2, name);
		addItemStmt.setFloat(3, maxPrice);
		addItemStmt.executeUpdate();
	}
}